﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Collections.Generic;

public partial class SurveyTest : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        if (fileUpload.PostedFile != null)
        {
            GetData(fileUpload.PostedFile.FileName);
        }
    }

    private void GetData(string fileName)
    {
        Dictionary<string, string> resultList = new Dictionary<string, string>();
        string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=yes'", fileName);
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
        DataSet myDataSet = new DataSet();
        dataAdapter.Fill(myDataSet);
        DataTable dataTable = myDataSet.Tables[0];
        var query = from r in dataTable.AsEnumerable()
                    select new
                    {
                        ResultID = r.Field<string>("ResultID"),
                        SubjectID = r.Field<double>("SubjectID"),
                        ItemID = r.Field<double>("ItemID"),
                    };
        query.GroupBy(p => p.SubjectID);
        Response.Write("SubjectID\tItemID<br>");
        foreach (var item in query)
        {
            if (resultList.Keys.Contains(item.SubjectID.ToString()))
            {
                resultList[item.SubjectID.ToString()] = resultList[item.SubjectID.ToString()] + item.ItemID.ToString();
            }
            else
            {
                resultList.Add(item.SubjectID.ToString(), item.ItemID.ToString());
            }
        }
        foreach (var item in resultList)
        {
            Response.Write(string.Format("{0}\t{1}\r\n<br>", item.Key, item.Value));
        }
    }
}
